package com.zwl.dao;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import org.apache.commons.beanutils.BeanUtils;

public abstract class BaseDao<T> {
	private String driverClass="oracle.jdbc.driver.OracleDriver";
	private String url="jdbc:oracle:thin:@localhost:1521:orcl";
	private String username="scott";
	private String password="123456";
	
	protected Connection conn=null;
	protected PreparedStatement pra=null;
	protected ResultSet rs=null;
	
	public Connection getConnection(){
		try {
			Class.forName(driverClass);
			return DriverManager.getConnection(url, username, password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public Connection getConnection2(){
		try {
			Context initContext = new InitialContext(); 
			DataSource ds = (DataSource) initContext.lookup("java:/comp/env/jdbc/auth"); 

			return ds.getConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	
	public void closeAll(ResultSet rs,Statement sta,Connection conn){
		try {
			if(rs!=null) rs.close();
			if(sta!=null) sta.close();
			if(conn!=null) conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public Object executeUpdate(String sql,String oprType,String[] columns,Object... paramsObjs){
		Connection conn=this.getConnection();
		PreparedStatement pra=null;
		ResultSet rs=null;
		try {
			
			if("insert".equalsIgnoreCase(oprType)){
				pra=conn.prepareStatement(sql,columns);
			}else{
				pra=conn.prepareStatement(sql);
			}
			
			if(paramsObjs!=null && paramsObjs.length>0){
				for(int i=0;i<paramsObjs.length;i++){
					pra.setObject(i+1, paramsObjs[i]);
				}
			}
		
			int rows=pra.executeUpdate();
			
			if("insert".equalsIgnoreCase(oprType)){
				rs=pra.getGeneratedKeys();
				int primaryKey=0;
				if(rs.next()){
					primaryKey=rs.getInt(1);
				}
				
				return new Integer[]{rows,primaryKey};
			}else{
				return new Integer(rows);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally{
			this.closeAll(rs, pra, conn);
		}
	}
	
	public ResultSet executeQuery(String sql,Object... paramsObjs) throws Exception{
		conn=this.getConnection();
		pra=conn.prepareStatement(sql);
		if(paramsObjs!=null && paramsObjs.length>0){
			for(int i=0;i<paramsObjs.length;i++){
				pra.setObject(i+1, paramsObjs[i]);
			}
		}
		ResultSet rs=pra.executeQuery();
		return rs;
	}
	
	public List<T> executeQuery2(String sql,Object... paramsObjs){
		List<T> list=new ArrayList<T>();
		Connection conn=this.getConnection();
		PreparedStatement pra=null;
		ResultSet rs=null;
		try {
			pra=conn.prepareStatement(sql);
			if(paramsObjs!=null && paramsObjs.length>0){
				for(int i=0;i<paramsObjs.length;i++){
					pra.setObject(i+1, paramsObjs[i]);
				}
			}
			rs=pra.executeQuery();
			while(rs.next()){
				list.add(this.getEntity(rs));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			this.closeAll(rs, pra, conn);
		}
		return list;
	}
	
	public List<T> executeQuery3(String sql,Map<String, String> columnsMap,Class outputClass,Object... paramsObjs){
		List<T> list=new ArrayList<T>();
		Connection conn=this.getConnection();
		PreparedStatement pra=null;
		ResultSet rs=null;
		try {
			pra=conn.prepareStatement(sql);
			if(paramsObjs!=null && paramsObjs.length>0){
				for(int i=0;i<paramsObjs.length;i++){
					pra.setObject(i+1, paramsObjs[i]);
				}
			}
			rs=pra.executeQuery();
			while(rs.next()){
				list.add(this.getEntity2(rs,columnsMap,outputClass));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			this.closeAll(rs, pra, conn);
		}
		return list;
	}
	
	public int getCount(String sql,Object... paramsObjs){
		Connection conn=this.getConnection();
		PreparedStatement pra=null;
		ResultSet rs=null;
		try {
			String countSql="select count(*) c from ("+sql+")";
			pra=conn.prepareStatement(countSql);
			if(paramsObjs!=null && paramsObjs.length>0){
				for(int i=0;i<paramsObjs.length;i++){
					pra.setObject(i+1, paramsObjs[i]);
				}
			}
			rs=pra.executeQuery();
			if(rs.next()){
				return rs.getInt("c");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			this.closeAll(rs, pra, conn);
		}
		return 0;
	}
	
	public abstract T getEntity(ResultSet rs);

	public T getEntity2(ResultSet rs,Map<String, String> columnsMap,Class outputClass){
		T resultObj=null;
		try {
			resultObj=(T) outputClass.newInstance();
			ResultSetMetaData rsd= rs.getMetaData();
			if(rsd!=null && rsd.getColumnCount()>0){
				for(int i=0;i<rsd.getColumnCount();i++){
					String columnName=rsd.getColumnName(i+1);
					Field field=null;
					String propertyName=columnName.toLowerCase();
					try {
						field=outputClass.getDeclaredField(propertyName);
					} catch (Exception e) {}
					if(field==null){
						StringBuffer propertyNameBf=new StringBuffer();
						String[] attrs=propertyName.split("_");
						if(attrs!=null && attrs.length>0){
							for(String str:attrs){
								if(propertyNameBf.length()>0){
									propertyNameBf.append(str.substring(0, 1).toUpperCase()+str.substring(1));
								}else{
									propertyNameBf.append(str);
								}
							}
						}
						propertyName=propertyNameBf.toString();
						try {
							field=outputClass.getDeclaredField(propertyName);
						} catch (Exception e) {}
					}
					if(field==null && columnsMap!=null){
						try {
							propertyName=columnsMap.get(columnName.toLowerCase());
							field=outputClass.getDeclaredField(propertyName);
						} catch (Exception e) {}
					}
					

					if(field!=null){
						String fieldName=field.getName();
						Object value=rs.getObject(columnName);
						if(value!=null){
							BeanUtils.copyProperty(resultObj, fieldName, value);
						}
						
					}
				}
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return resultObj;
	}
	
	public String getUpdateSql(String tableName){
		StringBuffer updateSql=new StringBuffer();
		Connection conn=this.getConnection();
		PreparedStatement pra=null;
		ResultSet rs=null;
		try {
			String sql="select * from "+tableName+" where rownum=1";
			ResultSetMetaData rsd= rs.getMetaData();
			if(rsd!=null && rsd.getColumnCount()>0){
				updateSql.append(" UPDATE "+rsd.getTableName(1));
				updateSql.append(" SET ");
				for(int i=0;i<rsd.getColumnCount();i++){
					String columnName=rsd.getColumnName(i+1);
					updateSql.append("    "+columnName+"=? ");
				}
			}
		}catch(Exception e){
			e.printStackTrace();
		} finally{
			this.closeAll(rs, pra, conn);
		}
		return updateSql.toString();
				
	}
	
}
